<?php

////////////////////////////////////////////////////////
//  DATABASE.PHP - TUTORIAL PART 2
////////////////////////////////////////////////////////

// This will connect us to our database...
//mysql_connect("SQL06.FreeMySQL.net", "softab", "parolainclar") or
mysql_connect("localhost", "root", "") or
   die("Could not connect: " . mysql_error());
mysql_select_db("domainone");

// The ext grid script will send  a task field which will specify what it wants to do
$task = '';
if ( isset($_POST['task'])){
	$task = $_POST['task'];
}
switch($task){
    case "LISTING":
        getList();
        break;	
	case "UPDATEPRES":
        updatePresident();
        break;
	case "SEARCH":
        searchPresidents();
        break;
    default:
        echo "{failure:true}";
        break;
}

function getList() 
{
	  $query = "SELECT * FROM presidents pr, parties pa WHERE pr.IDparty = pa.IDparty";
	  // Here we check if we have a query parameter :
	  if (isset($_POST['query'])){
	    $query .= " AND (pr.firstname LIKE '%".addslashes($_POST['query'])."%' OR pr.lastname LIKE '%".addslashes($_POST['query'])."%')";
	  }
	  $result = mysql_query($query);
	  $nbrows = mysql_num_rows($result);
	  $start = (integer) (isset($_POST['start']) ? $_POST['start'] : $_GET['start']);
	  $end = (integer) (isset($_POST['limit']) ? $_POST['limit'] : $_GET['limit']);
	  $limit = $query." LIMIT ".$start.",".$end;		
	  $result = mysql_query($limit); 	
	  if($nbrows>0){
		while($rec = mysql_fetch_array($result)){
            // render the right date format
			$rec['tookoffice']=codeDate($rec['tookoffice']);
			$rec['leftoffice']=codeDate($rec['leftoffice']);      
			$arr[] = $rec;
		}
		$jsonresult = JEncode($arr);
		echo '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
		} else {
		echo '({"total":"0", "results":""})';
	}
}

// Encodes a SQL array into a JSON formated string
function JEncode($arr){
    if (version_compare(PHP_VERSION,"5.2","<"))
    {    
        require_once("./JSON.php"); //if php<5.2 need JSON class
        $json = new Services_JSON();//instantiate new json object
        $data=$json->encode($arr);  //encode the data in json format
    } else
    {
        $data = json_encode($arr);  //encode the data in json format
    }
    return $data;
}

// Encodes a YYYY-MM-DD into a MM-DD-YYYY string
function codeDate ($date) {
	$tab = explode ("-", $date);
	$r = $tab[1]."/".$tab[2]."/".$tab[0];
	return $r;
}

function updatePresident()
{
    $IDpresident = $_POST['IDpresident'];
    $FirstName = addslashes($_POST['FirstName']);
    $LastName = addslashes($_POST['LastName']);
    $PartyName = $_POST['PartyName'];
    $TookOffice = $_POST['TookOffice'];
    $LeftOffice = $_POST['LeftOffice'];
    $Income = $_POST['Income'];
    
    // First, find the $IDparty
    $query = "SELECT IDParty FROM parties WHERE Name='".$PartyName."'";
    $result = mysql_query($query);
    if(mysql_num_rows($result)>0){
      $arr = mysql_fetch_array($result);
      $IDparty = $arr['IDParty'];
    } else {
      echo '0';      // failure
    }
   
    // Now update the president
    $query = "UPDATE presidents SET firstname = '$FirstName', lastname = '$LastName', tookoffice = '$TookOffice', leftoffice = '$LeftOffice', IDparty = '$IDparty', income='$Income' WHERE IDpresident=$IDpresident";
    $result = mysql_query($query);
    echo '1';        // success
}

function searchPresidents()
{
   $query = "SELECT * FROM presidents pr, parties pa WHERE pr.IDparty = pa.IDparty";

   $firstname = $_POST['firstname'];
   $lastname = $_POST['lastname'];
   $enteringoffice = $_POST['enteringoffice'];
   $leavingoffice = $_POST['leavingoffice'];
   $party  = $_POST['party'];
		
   if($firstname != ''){
      $query .= " AND firstname LIKE '%".$firstname."%'";
   };
   if($lastname != ''){
      $query .= " AND lastname LIKE '%".$lastname."%'";
   };
   if($party != ''){
      $query .= " AND pr.IDparty = '".$party."'";
   };
   if ($enteringoffice) {
        $query .= " AND tookoffice >= '".$enteringoffice."'";
     };
   if ($leavingoffice) {
        $query .= " AND leftoffice <= '".$leavingoffice."'";
     };
    
   $result = mysql_query($query);
   $nbrows = mysql_num_rows($result);  
   if($nbrows>0){
    while($rec = mysql_fetch_array($result)){
            // render the right date format
      $rec['tookoffice']=codeDate($rec['tookoffice']);
      $rec['leftoffice']=codeDate($rec['leftoffice']);      
      $arr[] = $rec;
    }
    $jsonresult = JEncode($arr);
    echo '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
   } else {
    echo '({"total":"0", "results":""})';
   }
}

?> 